
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 09/25/2011 02:21:43
-- Generated from EDMX file: C:\PersonalFinance\svn2\DAL\Repositories\EntityFramework4\Model.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [MoneyManagerNET];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_CategorySubCategory]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[SubCategorySet] DROP CONSTRAINT [FK_CategorySubCategory];
GO
IF OBJECT_ID(N'[dbo].[FK_CategoryTransaction]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[TransactionSet] DROP CONSTRAINT [FK_CategoryTransaction];
GO
IF OBJECT_ID(N'[dbo].[FK_ContragentTransaction]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[TransactionSet] DROP CONSTRAINT [FK_ContragentTransaction];
GO
IF OBJECT_ID(N'[dbo].[FK_SubCategoryTransaction]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[TransactionSet] DROP CONSTRAINT [FK_SubCategoryTransaction];
GO
IF OBJECT_ID(N'[dbo].[FK_CurrencyAccount]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[AccountSet] DROP CONSTRAINT [FK_CurrencyAccount];
GO
IF OBJECT_ID(N'[dbo].[FK_BankAccountDepositTransaction]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[TransactionSet] DROP CONSTRAINT [FK_BankAccountDepositTransaction];
GO
IF OBJECT_ID(N'[dbo].[FK_BankAccountWirthdrawTransaction]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[TransactionSet] DROP CONSTRAINT [FK_BankAccountWirthdrawTransaction];
GO
IF OBJECT_ID(N'[dbo].[FK_StockAccountStockTransaction]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[StockInvestmentSet] DROP CONSTRAINT [FK_StockAccountStockTransaction];
GO
IF OBJECT_ID(N'[dbo].[FK_BankAccount_inherits_Account]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[AccountSet_BankAccount] DROP CONSTRAINT [FK_BankAccount_inherits_Account];
GO
IF OBJECT_ID(N'[dbo].[FK_StockAccount_inherits_Account]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[AccountSet_StockAccount] DROP CONSTRAINT [FK_StockAccount_inherits_Account];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[CategorySet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[CategorySet];
GO
IF OBJECT_ID(N'[dbo].[SubCategorySet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[SubCategorySet];
GO
IF OBJECT_ID(N'[dbo].[AccountSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[AccountSet];
GO
IF OBJECT_ID(N'[dbo].[TransactionSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[TransactionSet];
GO
IF OBJECT_ID(N'[dbo].[ContragentSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ContragentSet];
GO
IF OBJECT_ID(N'[dbo].[CurrencySet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[CurrencySet];
GO
IF OBJECT_ID(N'[dbo].[StockInvestmentSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[StockInvestmentSet];
GO
IF OBJECT_ID(N'[dbo].[AccountSet_BankAccount]', 'U') IS NOT NULL
    DROP TABLE [dbo].[AccountSet_BankAccount];
GO
IF OBJECT_ID(N'[dbo].[AccountSet_StockAccount]', 'U') IS NOT NULL
    DROP TABLE [dbo].[AccountSet_StockAccount];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'CategorySet'
CREATE TABLE [dbo].[CategorySet] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(64)  NOT NULL
);
GO

-- Creating table 'SubCategorySet'
CREATE TABLE [dbo].[SubCategorySet] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(64)  NOT NULL,
    [CategoryId] int  NOT NULL
);
GO

-- Creating table 'AccountSet'
CREATE TABLE [dbo].[AccountSet] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(64)  NOT NULL,
    [Balance] decimal(18,0)  NOT NULL,
    [Status] bit  NOT NULL,
    [CurrencyId] nchar(5)  NULL,
    [Notes] nvarchar(max)  NULL
);
GO

-- Creating table 'TransactionSet'
CREATE TABLE [dbo].[TransactionSet] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [CategoryId] int  NOT NULL,
    [Amount] decimal(18,0)  NOT NULL,
    [Date] datetime  NOT NULL,
    [ContragentId] int  NOT NULL,
    [SubCategoryId] int  NULL,
    [Notes] nvarchar(512)  NULL,
    [Status] tinyint  NOT NULL,
    [ToAccountId] int  NULL,
    [BankAccountId] int  NULL
);
GO

-- Creating table 'ContragentSet'
CREATE TABLE [dbo].[ContragentSet] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(64)  NOT NULL
);
GO

-- Creating table 'CurrencySet'
CREATE TABLE [dbo].[CurrencySet] (
    [Id] nchar(8)  NOT NULL,
    [Name] nvarchar(64)  NOT NULL,
    [Symbol] nvarchar(8)  NOT NULL
);
GO

-- Creating table 'StockInvestmentSet'
CREATE TABLE [dbo].[StockInvestmentSet] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [StockName] nvarchar(64)  NOT NULL,
    [StockSymbol] nvarchar(16)  NOT NULL,
    [PurchasePrice] decimal(18,0)  NOT NULL,
    [CurrentPrice] decimal(18,0)  NOT NULL,
    [StockCount] int  NOT NULL,
    [StockAccountId] int  NOT NULL,
    [Commission] decimal(18,0)  NOT NULL,
    [Notes] nvarchar(max)  NULL,
    [Date] datetime  NOT NULL
);
GO

-- Creating table 'AccountSet_BankAccount'
CREATE TABLE [dbo].[AccountSet_BankAccount] (
    [Id] int  NOT NULL
);
GO

-- Creating table 'AccountSet_StockAccount'
CREATE TABLE [dbo].[AccountSet_StockAccount] (
    [Id] int  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Id] in table 'CategorySet'
ALTER TABLE [dbo].[CategorySet]
ADD CONSTRAINT [PK_CategorySet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'SubCategorySet'
ALTER TABLE [dbo].[SubCategorySet]
ADD CONSTRAINT [PK_SubCategorySet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'AccountSet'
ALTER TABLE [dbo].[AccountSet]
ADD CONSTRAINT [PK_AccountSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'TransactionSet'
ALTER TABLE [dbo].[TransactionSet]
ADD CONSTRAINT [PK_TransactionSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'ContragentSet'
ALTER TABLE [dbo].[ContragentSet]
ADD CONSTRAINT [PK_ContragentSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'CurrencySet'
ALTER TABLE [dbo].[CurrencySet]
ADD CONSTRAINT [PK_CurrencySet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'StockInvestmentSet'
ALTER TABLE [dbo].[StockInvestmentSet]
ADD CONSTRAINT [PK_StockInvestmentSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'AccountSet_BankAccount'
ALTER TABLE [dbo].[AccountSet_BankAccount]
ADD CONSTRAINT [PK_AccountSet_BankAccount]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'AccountSet_StockAccount'
ALTER TABLE [dbo].[AccountSet_StockAccount]
ADD CONSTRAINT [PK_AccountSet_StockAccount]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [CategoryId] in table 'SubCategorySet'
ALTER TABLE [dbo].[SubCategorySet]
ADD CONSTRAINT [FK_CategorySubCategory]
    FOREIGN KEY ([CategoryId])
    REFERENCES [dbo].[CategorySet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CategorySubCategory'
CREATE INDEX [IX_FK_CategorySubCategory]
ON [dbo].[SubCategorySet]
    ([CategoryId]);
GO

-- Creating foreign key on [CategoryId] in table 'TransactionSet'
ALTER TABLE [dbo].[TransactionSet]
ADD CONSTRAINT [FK_CategoryTransaction]
    FOREIGN KEY ([CategoryId])
    REFERENCES [dbo].[CategorySet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CategoryTransaction'
CREATE INDEX [IX_FK_CategoryTransaction]
ON [dbo].[TransactionSet]
    ([CategoryId]);
GO

-- Creating foreign key on [ContragentId] in table 'TransactionSet'
ALTER TABLE [dbo].[TransactionSet]
ADD CONSTRAINT [FK_ContragentTransaction]
    FOREIGN KEY ([ContragentId])
    REFERENCES [dbo].[ContragentSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ContragentTransaction'
CREATE INDEX [IX_FK_ContragentTransaction]
ON [dbo].[TransactionSet]
    ([ContragentId]);
GO

-- Creating foreign key on [SubCategoryId] in table 'TransactionSet'
ALTER TABLE [dbo].[TransactionSet]
ADD CONSTRAINT [FK_SubCategoryTransaction]
    FOREIGN KEY ([SubCategoryId])
    REFERENCES [dbo].[SubCategorySet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_SubCategoryTransaction'
CREATE INDEX [IX_FK_SubCategoryTransaction]
ON [dbo].[TransactionSet]
    ([SubCategoryId]);
GO

-- Creating foreign key on [CurrencyId] in table 'AccountSet'
ALTER TABLE [dbo].[AccountSet]
ADD CONSTRAINT [FK_CurrencyAccount]
    FOREIGN KEY ([CurrencyId])
    REFERENCES [dbo].[CurrencySet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CurrencyAccount'
CREATE INDEX [IX_FK_CurrencyAccount]
ON [dbo].[AccountSet]
    ([CurrencyId]);
GO

-- Creating foreign key on [ToAccountId] in table 'TransactionSet'
ALTER TABLE [dbo].[TransactionSet]
ADD CONSTRAINT [FK_BankAccountDepositTransaction]
    FOREIGN KEY ([ToAccountId])
    REFERENCES [dbo].[AccountSet_BankAccount]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_BankAccountDepositTransaction'
CREATE INDEX [IX_FK_BankAccountDepositTransaction]
ON [dbo].[TransactionSet]
    ([ToAccountId]);
GO

-- Creating foreign key on [BankAccountId] in table 'TransactionSet'
ALTER TABLE [dbo].[TransactionSet]
ADD CONSTRAINT [FK_BankAccountWirthdrawTransaction]
    FOREIGN KEY ([BankAccountId])
    REFERENCES [dbo].[AccountSet_BankAccount]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_BankAccountWirthdrawTransaction'
CREATE INDEX [IX_FK_BankAccountWirthdrawTransaction]
ON [dbo].[TransactionSet]
    ([BankAccountId]);
GO

-- Creating foreign key on [StockAccountId] in table 'StockInvestmentSet'
ALTER TABLE [dbo].[StockInvestmentSet]
ADD CONSTRAINT [FK_StockAccountStockTransaction]
    FOREIGN KEY ([StockAccountId])
    REFERENCES [dbo].[AccountSet_StockAccount]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_StockAccountStockTransaction'
CREATE INDEX [IX_FK_StockAccountStockTransaction]
ON [dbo].[StockInvestmentSet]
    ([StockAccountId]);
GO

-- Creating foreign key on [Id] in table 'AccountSet_BankAccount'
ALTER TABLE [dbo].[AccountSet_BankAccount]
ADD CONSTRAINT [FK_BankAccount_inherits_Account]
    FOREIGN KEY ([Id])
    REFERENCES [dbo].[AccountSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Id] in table 'AccountSet_StockAccount'
ALTER TABLE [dbo].[AccountSet_StockAccount]
ADD CONSTRAINT [FK_StockAccount_inherits_Account]
    FOREIGN KEY ([Id])
    REFERENCES [dbo].[AccountSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------